package org.sgx.dao.test.test1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Random;

import org.sgx.dao.ConnectionGiver;
import org.sgx.dao.DaoException;
import org.sgx.dao.JDBCConnectionGiver;
import org.sgx.dao.JDBCConnectionGiverNoClose;
import org.sgx.dao.util.SQLUtil;
import org.sgx.dao.util.Util;

import junit.framework.TestCase;

public class SQLUtilTest1 extends TestCase {
	
	private static ConnectionGiver connectionGiver;

	public static ConnectionGiver getValidConnectionGiver() throws DaoException {
		if(connectionGiver==null) {
		//common jdbc connection parameters
		String url = "jdbc:mysql://localhost:3306/";
		String dbName = "daotest1";
		String driver = "com.mysql.jdbc.Driver";
		String userName = "root";
		String password = "root";
		
		//now create a connection giver this object provides with connection and some 
		//other util operations needed by the framework. in this case we use a common jdbc connection to a mysql database:
		connectionGiver = new JDBCConnectionGiverNoClose(url, driver, dbName, userName, password);
		}
		return connectionGiver;		
	}
	public static SQLUtil getSqlUtil() throws DaoException {
		return new SQLUtil(getValidConnectionGiver());
	}
	public void testMap1() throws DaoException {
		
		SQLUtil sql = getSqlUtil();
		
		String mapName = "map1";
		assertTrue(sql.mapGet(mapName, "k")==null);
		sql.mapPut(mapName, "k", "v");
		assertTrue(sql.mapGet(mapName, "k").equals("v"));
		
		String mapName2 = "map2";
		assertTrue(sql.mapGet(mapName2, "k2")==null);
		assertTrue(sql.mapGet(mapName2, "k")==null);
		assertTrue(sql.mapGet(mapName, "k2")==null);
		sql.mapPut(mapName2, "k2", "v2");
		assertTrue(sql.mapGet(mapName, "k2")==null);
		assertTrue(sql.mapGet(mapName2, "k2").equals("v2"));
		
		sql.mapClean(mapName);
		assertTrue(sql.mapGet(mapName, "k")==null);
		assertTrue(sql.mapGet(mapName2, "k2").equals("v2"));
		sql.mapClean(mapName2);
		assertTrue(sql.mapGet(mapName2, "k2")==null);
		
	}
	public void test1() throws DaoException {
		
		SQLUtil sql = getSqlUtil();
		String tableName = "test1Table1";
		
		if(sql.tableExists(tableName)) {
			sql.tableDrop(tableName);
		}

		assertTrue(!sql.tableExists(tableName));
		
		sql.tableCreate(tableName, Util.toOrderedMap(
			"col1", SQLUtil.TYPE_STRING, 
			"col2", SQLUtil.TYPE_BOOLEAN, 
			"col3", SQLUtil.TYPE_DATETIME, 
			"col4", SQLUtil.TYPE_LONG
		));
		assertTrue(sql.tableExists(tableName));
		assertTrue(sql.queryAll(tableName).size()==0);
		assertTrue(sql.queryOne(tableName, "col1", "s")==null);
		
		Date date1 = new Date();
//		System.out.println("time1: "+date1.getTime());
		sql.insert(Util.toOrderedMap(
			"col1", "val1", 
			"col2", false, 
			"col3", date1,
			"col4", 999999l
		), tableName);
		assertTrue(sql.queryAll(tableName).size()==1);
		
		List<Map> o1 = sql.queryAll(tableName);
		assertEquals("val1", o1.get(0).get("col1"));
		assertEquals(false, o1.get(0).get("col2"));
//		System.out.println(date1.getClass()+" - "+o1.get(0).get("col3").getClass()+" - "+date1.getTime()+" - "+((Date)o1.get(0).get("col3")).getTime());
		
		System.out.println(Util.toDateTime(date1)+" - "+Util.toDateTime((Date) o1.get(0).get("col3")));
		
		Date date1_ = (Date) o1.get(0).get("col3");
		assertEquals(Util.toDateTime(date1), Util.toDateTime(date1_));//note that date1.equals(date1_) is false because for some reason i know getTime() is different number returned by sql ...
		
		Object otherNumber =  o1.get(0).get("col4");
		assertEquals(999999l,otherNumber);
		
		
		//add a lot more objects and see if we still can find ours.
		for(int i=0; i<10; i++) {
			sql.insert(Util.toOrderedMap(
				"col1", "value"+i, 
				"col2", new Random().nextInt()%2==0, 
				"col3", new Date(),
				"col4", i
			), tableName);
		}
		
		//queries
		assertTrue(sql.queryAll(tableName).size()==11);
		assertTrue(sql.queryOne(tableName, "col4", 999999l).get("col1").equals("val1"));
		for(int i=0; i<10; i++) {
			System.out.println(sql.queryOne(tableName, "col1", "value"+i).get("col4").getClass()+" - ");
			assertEquals(new Long(i), sql.queryOne(tableName, "col1", "value"+i).get("col4"));
//			assertTrue(.equals(i));
		}
		
		List<Map> l = sql.query(tableName, Util.<Object>toOrderedMap("col1", "value1"));
		assertTrue(l.size()==1);
		assertEquals(new Long(1), l.get(0).get("col4"));
//		assertTrue(l.get(0).get("col4").equals(new Long(1)));
		
		
		sql.update(tableName, Util.<Object>toOrderedMap("col4", 999999l), 
			Util.<Object>toOrderedMap("col1", "helloworld"));
		assertEquals("helloworld", sql.queryOne(tableName, "col4", 999999l).get("col1"));
		
		sql.delete(tableName, Util.<Object>toOrderedMap("col4", 999999l));
		assertEquals(null, sql.queryOne(tableName, "col4", 999999l));
		
		//update all
		sql.update(tableName, null,  Util.<Object>toOrderedMap("col4", 87l));
		List<Map> all = sql.queryAll(tableName);
		for(Map m : all) {
			assertEquals(87l, m.get("col4"));
		}
		
		sql.tableDropAll();
	}
	
	
	
	public void testTableOps() throws DaoException {
		SQLUtil sql = getSqlUtil();
		String tableName = "testTableOpsTest1";
		try {
			sql.tableDrop(tableName);
		} catch (Exception e) {
		}
		assertTrue(!sql.tableExists(tableName));
		
		sql.tableCreate(tableName, Util.<String>toOrderedMap("col1", "BIGINT"));
		assertTrue(sql.tableExists(tableName));
		assertTrue(sql.tableGetColumns(tableName).keySet().size()==1);
		assertTrue(sql.tableGetColumns(tableName).get("col1").equals(java.sql.Types.BIGINT));		
		assertTrue(sql.tableHasColumn(tableName, "col1"));
		assertTrue(!sql.tableHasColumn(tableName, "col2"));
		
		sql.tableAddColumn(tableName, "col2", "VARCHAR(30)");
		assertTrue(sql.tableGetColumns(tableName).keySet().size()==2);
		assertTrue(sql.tableHasColumn(tableName, "col2"));
		assertTrue(sql.tableGetColumns(tableName).get("col2").equals(java.sql.Types.VARCHAR));
		
		sql.tableDropColumn(tableName, "col2");
		assertTrue(sql.tableGetColumns(tableName).keySet().size()==1);
		assertTrue(sql.tableHasColumn(tableName, "col1"));
		assertTrue(!sql.tableHasColumn(tableName, "col2"));
	}
	
	
	public void testBlobStuff() throws DaoException {
		SQLUtil sql = getSqlUtil();
		String tableName = "testBlobStuffTest1";
		try {
			sql.tableDrop(tableName);
		} catch (Exception e) {
		}
		sql.tableCreate(tableName, Util.<String>toOrderedMap(
				"col1", "BIGINT", "col2", "BLOB"));
		sql.insert(Util.toOrderedMap(
			"col1", 123l, 
			"col2", "data123".getBytes()
		), tableName);
		
		List<Map> r = sql.query(tableName, Util.<Object>toOrderedMap("col1", 123l));
		assertTrue(r.size()==1);
		assertEquals("data123", new String((byte[])r.get(0).get("col2")));
	}

	
	public void testCounter() throws DaoException {
		SQLUtil sql = getSqlUtil();
		String counterTableName = "org_sgx_dao_counter";
		sql.tableDrop(counterTableName);
		assertTrue(!sql.tableExists(counterTableName));
		assertEquals(1l, sql.incrementCounter("class1"));
		assertEquals(2l, sql.incrementCounter("class1"));
		assertEquals(1l, sql.incrementCounter("class2"));
	}
	
}
